Merveilleux SQL!

PGDay France Lyon
2019-06-19

Qui suis-je ?

• Lætitia Avrot
• Membre du CoC PostgreSQL
• Co-fondatrice de #PostgresWomen
• Webedia Gaming
• @l_avrot

Les merveilles méconnues du SQL

Différent

• Langage déclaratif
• Avec indentation
• Avec retours à la ligne
• Avec commentaires

Turing complete!

Démonstration sur le wiki de PostgreSQL: https://wiki.postgresql.org/wiki/
Turing_Machine_(with_recursive)

Advent of code 2017: https://github.com/xocolatl/advent-of-code/tree/master/2017

Historique

• SQL-86
• SQL-89 annule et remplace SQL-86
• SQL-92
• SQL:1999, SQL:2003, SQL:2006
• SQL:2008, SQL:2011, SQL:2013
• SQL:2016

pgexercises.com

Autojointure

(externe)

select
  mem."firstname"
    || ' ' ||
    mem."surname",
  memref."firstname"
    || ' ' ||
    memref."surname"
from
  "members" as mem
left outer join
  "members" as memref
on mem."recommendedby" = memref."memid"

Autojointure

• Jointure d'une table avec elle-même
• Possible en jointure externe à gauche, à droite et en jointure interne
• depuis toujours

Sous-requête

select
  mem."firstname"
    || ' ' ||
    mem."surname",
  (
    select
      memref."firstname"
        || ' ' ||
        memref."surname"
    from members as memref
    where
      mem."recommendedby" = memref."memid"
  )
from members as mem

Sous-requête

(dans la clause select)

• Affiche directement le résultat d'une sous-requête
• Uniquement pour les sous-requêtes scalaires
• SQL:1999

Sous-requête

(dans la clause select)

select
  ( select
      "columnName"
    from tableName
    where ...
  )
            

CTE

with memref as
(
  select
    "firstname" || ' ' || "surname" as refname
  from
    members
)
select
  mem."firstname" || ' ' || mem."surname",
  memref.refname
from
  members as mem
left outer join memref
on mem."recommendedby" = memref."memid"

Common Table Expressions (CTE)

• Permet de déclarer des sous-requêtes
• Clause with
• SQL:1999

Common Table Expressions (CTE)

with "mySubqueryName"
  (
    column1,
    column2,
    ...
  )
as
  (
    whatever subquery
  )
select
from "mySubqueryName"
...
            

select
  mem."firstname"
    || ' ' ||
    mem."surname",
  memref."firstname"
    || ' ' ||
    memref."surname"
from
  "members" as mem
left outer join
  "members" as memref
on mem."recommendedby" = memref."memid"

select
  mem."firstname",
  mem."surname",
  (
    select
      memref."firstname",
      memref."surname",
    from members as memref
    where
      mem."recommendedby" = memref."memid"
)
from members as mem

Jointure Laterale

select
  mem."firstname",
  mem."surname",
    memref."firstname",
  memref."surname"
from members as mem
left join lateral
  (select "firstname",
     "surname"
   from members as mentors
   where
     mentors.memid = mem.recommendedby) as memref
on true

Jointure Laterale

• À utiliser lorsqu'on pense "pour chaque ligne, je voudrais ..."
• Attention à l'addiction!
• Depuis SQL:1999

Or

select
  "firstname",
  "surname"
from members
where
  "firstname" = 'David'
or
  "firstname" = 'Tim'
or
  "firstname" = 'Darren'

In

select
  "firstname",
  "surname"
from members
where
  "firstname" in ('David', 'Tim', 'Darren')

Values

insert into ("column1",...) values (...);

Values

select
  "firstname",
  "surname"
from members
inner join
  values
    (('David'),('Tim'),('Darren'))
  as people(firstname)
on members."firstname" = people."firstname"

Values

• Pour créer une table de constantes
• Réduire le trafic réseau lors des inserts
• Optimiser certaines requêtes in
• Depuis SQL:92

NOT IN

select surname,
  firstname
from members
where "memid" not in
  (
    select memid
    from bookings
  )

NOT EXISTS

select surname,
  firstname
from members
where not exists
  (
    select 1
    from bookings
    where bookings.memid = members.memid
  )

ANTI-JOIN

select surname,
  firstname
from members
left outer join "bookings"
on "members"."memid" = "bookings"."memid"
where "bookings".memid is null

Rollup

select facid,
  extract(month from starttime),
  sum(slots) as slots
from cd.bookings
where extract(year from starttime) = 2012
group by rollup("facid", extract(month from "starttime"))
order by "facid",
  extract(month from "starttime")

Rollup

• Données hiérarchisées
• Différent de CUBE
• Depuis SQL:99

Sous-requête

select (
  select count(*) from members
) as count,
firstname, surname
	from members
order by joindate

Window functions

select count(*) 
  over(),
  "firstname",
  "surname"
from members
order by joindate 

Window functions

• Aggrégats en dehors des colonnes affichées
• SQL:2003
• SQL:2011: LEAD, LAG...